import requests
from bs4 import BeautifulSoup
import pandas as pd
import sqlite3
import re
from beautifultable import BeautifulTable

def get_weather_data(url):
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
    }
    try:
        res = requests.get(url,headers=headers)
        res.encoding='utf-8'
        soup = BeautifulSoup(res.text, 'html.parser')
        lis = soup.select("ul[class='t clearfix'] li")
        table = BeautifulTable()
        table.set_style(BeautifulTable.STYLE_COMPACT)
        table.columns.header = ["城市", "日期", "天气", "温度"]
        table.columns.alignment = BeautifulTable.ALIGN_CENTER
        db = WeatherDB()
        db.openDB()
        for li in lis:
            try:
                date = li.select('h1')[0].text
                weather = li.select('p[class="wea"]')[0].text
                # # 处理温度可能的结构差异（避免索引错误）
                temp_span = li.select('p[class="tem"] span')
                temp_i = li.select('p[class="tem"] i')[0].text
                temp = temp_span[0].text+'/'+temp_i if temp_span else temp_i
                table.rows.append(["北京",date,weather,temp])
                db.insert('北京', date, weather, temp)
                print(f"✓ 成功插入数据: 北京 - {date} - {weather} - {temp}")
            except Exception as err:
                print(err)
        print(table)
        print()
    except Exception as e:
        print(f"获取天气数据失败: {e}")
    db.closeDB()
    print(f"✅ 数据已成功存储到数据库 weathers.db")
class WeatherDB:
    def openDB(self):
        self.con = sqlite3.connect("weathers.db")
        self.cursor = self.con.cursor()
        try:
            self.cursor.execute(
                "create table weathers (wCity varchar(16),wDate varchar(16),wWeather varchar(64),wTemp varchar(32),constraint pk_weather primary key (wCity,wDate))")
        except:
            self.cursor.execute("delete from weathers")

    def closeDB(self):  # 修正：将closeDB方法与openDB同级，属于WeatherDB类
        self.con.commit()
        self.con.close()

    def insert(self, city, date, weather, temp):
        try:
            self.cursor.execute("insert into weathers (wCity,wDate,wWeather,wTemp) values (?,?,?,?)",
                                (city, date, weather, temp))
        except Exception as err:
            print(err)


url="https://www.weather.com.cn/weather/101010100.shtml"
get_weather_data(url)



